<?php
/**
 * Created by PhpStorm.
 * User: yuqing
 * Date: 15-8-11
 * Time: 下午3:22
 */
class Controller_outland extends Controller{
    public function Action_outlandorder(){
        echo date('Y-m-d H:i:s')."开始执行订单更新操作\n";

        set_time_limit(0);
        ini_set('memory_limit', '512M');
        require_once(Kohana::find_file('vendor', 'phprpc/phprpc_client'));

        //1.获取进销存半年来海外仓没有的有效的采购单号
        $puIds = $this->getNewPuIds();
        if(!empty($puIds)){
            //2.根据采购单号获取进销存采购单信息并插入到海外仓采购单表，并获取插入ID
            $record = $this->order2db($this->getOrderInfo($puIds));
            //3.根据采购单号获取对应有效的采购单明细信息，结合获取的插入海外仓后返回的ID，以及海外仓对应goods表SKU对应的ID，插入到海外仓采购单明细表
            //4.根据所插入的SKU和海外仓goods表对比获取海外仓不存在的SKU
            if(!empty($record)) {
                $noSkus = $this->detail2db($this->getOrderDetail(array_keys($record)), $record);
                if(!empty($noSkus)) {
                    //5.从SKU基础资料提取这些不存在的SKU的基本信息，插入到海外仓goods表，返回SKU=>ID形式数组
                    /*$client = new PHPRPC_Client();
                    $client->useService("http://192.168.5.5:802/profitapi/execute");*/

                    $noSkusStr = "'".join("','", $noSkus)."'";
                    foreach(array('skusystem_length', 'skusystem_width', 'skusystem_height', 'skusystem_weight', 'skusystem_price') as $tbl) {
                        $sql = "SELECT `sku`,`num` FROM `{$tbl}` WHERE `sku` IN ({$noSkusStr})";
                        $result = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array();
                        $$tbl = checkstore::array_columns($result, 'num', 'sku');
                    }

                    foreach(array('skusystem_sku_desc_main'=>'skusystem_desc_main', 'skusystem_sku_cnname'=>'skusystem_cnname') as $lTbl=>$rTbl){
                        $sql = "SELECT `sku` , `name` FROM `{$lTbl}` LEFT JOIN `{$rTbl}` ON `{$lTbl}`.`attr_id` = `{$rTbl}`.`id`";
                        $sql .= " WHERE `sku` IN ({$noSkusStr})";
                        $result = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array();
                        $$lTbl = checkstore::array_columns($result, 'name', 'sku');
                        tool::slashesDeep($$lTbl);
                    }

                    $goods_img = 'data/upload_picture.gif';
                    $rels = array();
                    foreach($noSkus as $no){
                        $cost = isset($price[$no]) ? $price[$no] : 0;
                        $l = isset($length[$no]) ? $length[$no] : 0;
                        $w = isset($width[$no]) ? $width[$no] : 0;
                        $h = isset($height[$no]) ? $height[$no] : 0;
                        $weight = isset($weight[$no]) ? $weight[$no] : 0;
                        $comment = isset($sku_desc_main[$no]) ? $sku_desc_main[$no] : 0;
                        $goods_name = isset($sku_cnname[$no]) ? $sku_cnname[$no] : 0;

                        $sql = 'INSERT INTO `myr_goods`(`goods_sn`, `keeptime`,`status`,`cost`,`l`,`w`,`h`,`comment`,`nature`,`weight`,`deliver_type`,`SKU`,`goods_name`,`is_share`,`is_control`,`goods_img`)';
                        $sql .= 'VALUES(';
                        $sql .= "'{$no}', 2145801600, 1, {$cost}, {$l}, {$w}, {$h}, '{$comment}', 0, {$weight}, 0, '{$no}', '{$goods_name}', 0, 1, '{$goods_img}'";
                        $sql .= ')';

                        list($id, ) = DB::query(Database::INSERT, $sql)->execute('outland');
                        $rels[$no] = $id;

                        unset($id);
                    }
                    //6.根据$noSku和插入海外常goods表返回的数组批量更新采购单明细表goods_id字段
                    if(!empty($rels)){
                        $ditailIdsStr = '';

                        $sql = 'UPDATE `myr_box_goodsdetail` SET `goods_id` = (CASE `id` ';

                        $have = array_keys($rels);

                        foreach($noSkus as $id=>$sku){
                            if(in_array($sku, $have)){
                                $sql .= " WHEN {$id} THEN {$rels[$sku]} ";
                                $ditailIdsStr .= empty($ditailIdsStr) ? $id : ','.$id;
                            }
                        }

                        if(!empty($ditailIdsStr)){
                            $sql .= "END) WHERE `id` IN({$ditailIdsStr})";
                            DB::query(Database::UPDATE, $sql)->execute('outland');
                            unset($sql, $ditailIdsStr);
                        }
                    }

                    unset($noSkus, $rels, $sku_desc_main, $sku_cnname);
                }
            }
            echo  date('Y-m-d H:i:s')."新采购单插入成功\n";
        }

        unset($puIds);
        $existPuIds = $this->getOutlandExistPuIds();
        if(!empty($existPuIds)) {
            $puIdsChunks = array_chunk($existPuIds, 5000);
            unset($existPuIds);

            foreach($puIdsChunks as $puIdsChunk) {
                $this->orderUpdate2db($this->getUpdateOrderInfo($puIdsChunk));

                $sql = 'UPDATE `myr_box_goodsdetail` ';
                $delivery_number_str = $real_receive_number_str = $status_str = ' CASE `id` ';
                //11.根据采购单到进销存获取采购单明细array(采购单号=>array(sku))，根据采购单从海外仓明细获取采购单明细（形成一个数组array(采购单号=>array(sku1=>海外仓明细ID1， sku2=>海外仓明细ID2))）

                $outlandDetails = $this->getUpdateOutlandOrderDetailInfo($puIdsChunk);
                $puSkuDetailIds = $outlandPuSkusArr = $newSku = array();
                foreach($outlandDetails as $outlandPuDetail) {
                    $outlandPuSkusArr[$outlandPuDetail['order_sn']][] = $outlandPuDetail['goods_sn'];
                    $puSkuDetailIds[$outlandPuDetail['order_sn']][$outlandPuDetail['goods_sn']] = $outlandPuDetail['id'];
                }

                $idsStr = '';
                $newerpPuDetails = $this->getUpdateNewerpPuDetailInfo($puIdsChunk);
                foreach($newerpPuDetails as $newerpDetail) {
                    if(in_array($newerpDetail['sku'], $outlandPuSkusArr[$newerpDetail['purchaseorder_id']]))
                    {
                        $delivery_number_str .= " WHEN {$puSkuDetailIds[$newerpDetail['purchaseorder_id']][$newerpDetail['sku']]} THEN {$newerpDetail['quantity']} ";
                        $real_receive_number_str .= " WHEN {$puSkuDetailIds[$newerpDetail['purchaseorder_id']][$newerpDetail['sku']]} THEN {$newerpDetail['recieve_quantity']} ";
                        $status_str .= " WHEN {$puSkuDetailIds[$newerpDetail['purchaseorder_id']][$newerpDetail['sku']]} THEN {$newerpDetail['status']} ";
                        $idsStr .= $idsStr == '' ? $puSkuDetailIds[$newerpDetail['purchaseorder_id']][$newerpDetail['sku']] : ','.$puSkuDetailIds[$newerpDetail['purchaseorder_id']][$newerpDetail['sku']];
                        continue;
                    }
                    else $newSku[$newerpDetail['purchaseorder_id']][] = $newerpDetail['sku'];
                }

                if($delivery_number_str != ' CASE `id` ') {
                    //$sql .= " SET `delivery_number` = ($delivery_number_str END), `real_receive_number` = ($real_receive_number_str END), `iscomplete` = ($status_str END) WHERE `id` IN ($idsStr)";
                    $sql .= " SET `delivery_number` = ($delivery_number_str END), `iscomplete` = ($status_str END) WHERE `id` IN ($idsStr)";
                    DB::query(Database::UPDATE, $sql)->execute('outland');
                    unset($sql);
                }

                if(!empty($newSku)) {
                    foreach($newSku as $puId=>$new) {
                        foreach($new as $_sku) {
                            $sql = "SELECT `delivery_id` FROM `myr_deliver` WHERE `order_sn` = '{$puId}'";
                            $boxId = DB::query(Database::SELECT, $sql)->execute('outland')->get('delivery_id', 0);

                            if($boxId) {
                                $sql = "SELECT `sku`,`sku_name`,`quantity`,`single_price`,`recieve_quantity` FROM `purchaseorder_details` WHERE `purchaseorder_id` = {$puId} AND `sku` = '{$_sku}' AND `status` != 100";
                                $crrent = DB::query(Database::SELECT, $sql)->execute('newerp')->current();

                                if(!empty($crrent)) {
                                    /*$sql = "INSERT INTO `myr_box_goodsdetail`
                                    (`goods_sn`,`box_id`,`delivery_number`,`value`,`goods_name`,`real_receive_number`,`goods_id`,`boxnum`)
                                    VALUES ('{$_sku}' ,{$boxId}, {$crrent['quantity']}, {$crrent['single_price']}, '{$crrent['sku_name']}', {$crrent['recieve_quantity']}, 0, 0)";*/

                                    $sql = "INSERT INTO `myr_box_goodsdetail`
                                    (`goods_sn`,`box_id`,`delivery_number`,`value`,`goods_name`,`real_receive_number`,`goods_id`,`boxnum`)
                                    VALUES ('{$_sku}' ,{$boxId}, {$crrent['quantity']}, {$crrent['single_price']}, '{$crrent['sku_name']}', 0, 0, 0)";
                                    list($detail_id, ) = DB::query(Database::INSERT,$sql)->execute('outland');

                                    $sql = "SELECT `goods_id` FROM `myr_goods` WHERE `goods_sn` = '{$_sku}'";
                                    $goodId = DB::query(Database::SELECT, $sql)->execute('outland')->get('goods_id');
                                    if($goodId) {
                                        $sql = 'UPDATE `myr_box_goodsdetail` SET `goods_id` = '.$goodId.' WHERE `id` = '.$detail_id;
                                        DB::query(Database::UPDATE, $sql)->execute('outland');
                                    }else{
//                                        $client = new PHPRPC_Client();
//                                        $client->useService("http://192.168.5.5:802/profitapi/execute");

                                        $noSkusStr = "'".join("','", array($_sku))."'";
                                        foreach(array('skusystem_length', 'skusystem_width', 'skusystem_height', 'skusystem_weight', 'skusystem_price') as $tbl) {
                                            $sql = "SELECT `sku`,`num` FROM `{$tbl}` WHERE `sku` IN ({$noSkusStr})";
                                            $result = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array();
                                            $$tbl = checkstore::array_columns($result, 'num', 'sku');
                                        }
                                        foreach(array('skusystem_sku_desc_main'=>'skusystem_desc_main', 'skusystem_sku_cnname'=>'skusystem_cnname') as $lTbl=>$rTbl){
                                            $sql = "SELECT `sku` , `name` FROM `{$lTbl}` LEFT JOIN `{$rTbl}` ON `{$lTbl}`.`attr_id` = `{$rTbl}`.`id`";
                                            $sql .= " WHERE `sku` IN ({$noSkusStr})";
                                            $result = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array();
                                            $$lTbl = checkstore::array_columns($result, 'name', 'sku');
                                            tool::slashesDeep($$lTbl);
                                        }

                                        $goods_img = 'data/upload_picture.gif';
                                        $cost = isset($price[$_sku]) ? $price[$_sku] : 0;
                                        $l = isset($length[$_sku]) ? $length[$_sku] : 0;
                                        $w = isset($width[$_sku]) ? $width[$_sku] : 0;
                                        $h = isset($height[$_sku]) ? $height[$_sku] : 0;
                                        $weight = isset($weight[$_sku]) ? $weight[$_sku] : 0;
                                        $comment = isset($sku_desc_main[$_sku]) ? $sku_desc_main[$_sku] : 0;
                                        $goods_name = isset($sku_cnname[$_sku]) ? $sku_cnname[$_sku] : 0;

                                        $sql = 'INSERT INTO `myr_goods`(`goods_sn`, `keeptime`,`status`,`cost`,`l`,`w`,`h`,`comment`,`nature`,`weight`,`deliver_type`,`SKU`,`goods_name`,`is_share`,`is_control`,`goods_img`)';
                                        $sql .= 'VALUES(';
                                        $sql .= "'{$_sku}', 2145801600, 1, {$cost}, {$l}, {$w}, {$h}, '{$comment}', 0, {$weight}, 0, '{$_sku}', '{$goods_name}', 0, 1, '{$goods_img}'";
                                        $sql .= ')';

                                        list($id, ) = DB::query(Database::INSERT, $sql)->execute('outland');

                                        $sql = 'UPDATE `myr_box_goodsdetail` SET `goods_id` = '.$id.' WHERE `id` = '.$detail_id;
                                        DB::query(Database::UPDATE, $sql)->execute('outland');
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }

    /**
     * @param array $pus
     * 批量更新海外仓采购单
     */
    public function orderUpdate2db($pus = array()) {
        if(!empty($pus)) {
            $puIdsStr = '';
            $sql = 'UPDATE `myr_deliver` SET';

            $delivery_status = "`delivery_status` = (CASE `order_sn` ";
            $shiping_fee = "`shiping_fee` = (CASE `order_sn` ";
            foreach($pus as $puId=>$pu) {
                $delivery_status .= " WHEN {$puId} THEN {$pu['status']} ";
                $shiping_fee .= " WHEN {$puId} THEN {$pu['transportation_expense']} ";
                $puIdsStr .= $puIdsStr == '' ? $puId : ','.$puId;
            }
            $delivery_status .= " END),";
            $shiping_fee .= " END)";

            $sql .= $delivery_status.$shiping_fee." WHERE `order_sn` IN({$puIdsStr})";

            DB::query(Database::UPDATE, $sql)->execute('outland');

            $sql = '';
        }
    }

    //从进销存获取某段时间的海外仓采购单
    protected function getOrderInfo($orderid, $type = NULL){

        $sql = 'SELECT
          `id`,`purchaseorder_date`,`transportation_expense`,`purchase_id`
          FROM purchaseorders
          WHERE `id` IN ('.join(',',$orderid).')';

        return empty($type) ? DB::query(Database::SELECT,$sql)->execute('newerp')->as_array() : DB::query(Database::SELECT,$sql)->execute('newerp')->as_array('id');
    }

    /**
     * @param $orderIds
     * @return mixed
     * 获取要更新的采购单信息
     */
    protected function getUpdateOrderInfo($orderIds) {
        $sql = 'SELECT
          `id`,`transportation_expense`,IF(`status`=100, 10, 1) AS `status`
          FROM purchaseorders
          WHERE `id` IN ('.join(',',$orderIds).')';

        return DB::query(Database::SELECT,$sql)->execute('newerp')->as_array('id');
    }

    /**
     * @param $orderIds
     * @return mixed
     * 获取要更新的采购单明细信息
     */
    protected function getUpdateOrderDetailInfo($orderIds) {
        $sql = 'SELECT `purchaseorder_id`,UPPER(`sku`) AS `sku`,`quantity`,`recieve_quantity`,`single_price`
                FROM purchaseorder_details
                WHERE `purchaseorder_id` IN ('.join(',',$orderIds).')';

        return DB::query(Database::SELECT,$sql)->execute('newerp')->as_array();
    }

    public function getUpdateOutlandOrderDetailInfo($puIds) {
        $sql = "SELECT `p`.`order_sn`, `pd`.`id`, `pd`.`goods_sn`
            FROM `myr_deliver` AS `p`
            LEFT JOIN `myr_box_goodsdetail` AS `pd`
            ON (`p`.`delivery_id` = `pd`.`box_id`)
            WHERE `p`.`order_sn` IN (".join(',', $puIds).")";

        return DB::query(Database::SELECT,$sql)->execute('outland')->as_array();
    }

    public function getUpdateNewerpPuDetailInfo($puIds) {
        $sql = "SELECT `purchaseorder_id`,UPPER(`sku`) AS `sku`,`quantity`,`single_price`,`recieve_quantity`,`ware_quantity`,IF(`status` = 100, 10, 0) AS `status`
            FROM `purchaseorder_details`
            WHERE `purchaseorder_id` IN (".implode(',', $puIds).")";

        return DB::query(Database::SELECT,$sql)->execute('newerp')->as_array();
    }

    /*
     * 根据进销存多个采购单ID批量找出进销存采购明细信息
     */
    protected function getOrderDetail($orderId){
        if(is_array($orderId) AND !empty($orderId)){
            $sql = 'SELECT `purchaseorder_id`,UPPER(`sku`) AS `sku`,`sku_name`,`quantity`,`recieve_quantity`,`single_price`
                FROM purchaseorder_details
                WHERE `status` != 100
                AND `purchaseorder_id` IN ('.join(',',$orderId).')';
            $res = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array();
        }
        return !empty($res) ? $res : array();
    }
    //吧订单信息插入到海外仓数据库 tableName: myr_deliver
    protected function order2db($orders = array()){
        $record = array();
        foreach($orders as $order){
            $sql = 'INSERT INTO `myr_deliver`
                (`delivery_status`,`a_depot`,`delivery_time`,`shiping_fee`,`user_id`,`order_sn`)
                VALUES
                (1,1,'.strtotime($order['purchaseorder_date']).','.$order['transportation_expense'].',1,'.$order['id'].")";
            $result = DB::query(Database::INSERT,$sql)->execute('outland');
            $record[$order['id']] = $result[0];
        }
        //  key ===   采购单ID
        //value ===   outland 采购单primarykey
        return $record;
    }
    //采购明细插入到 海外仓数据库 tableName:myr_box_goodsdetail
    protected function detail2db($details = array(), $record = array()){
        $skus = array();

        if(!empty($details)){
            foreach($details as $detail){
                tool::slashesDeep($detail);
                if(isset($record[$detail['purchaseorder_id']])){
                    /*$sql = "INSERT INTO `myr_box_goodsdetail`
                        (`goods_sn`,`box_id`,`delivery_number`,`value`,`goods_name`,`real_receive_number`,`goods_id`,`boxnum`)
                        VALUES ('{$detail['sku']}' ,{$record[$detail['purchaseorder_id']]}, {$detail['quantity']}, {$detail['single_price']}, '{$detail['sku_name']}', {$detail['recieve_quantity']}, 0, 0)";
                    */$sql = "INSERT INTO `myr_box_goodsdetail`
                        (`goods_sn`,`box_id`,`delivery_number`,`value`,`goods_name`,`real_receive_number`,`goods_id`,`boxnum`)
                        VALUES ('{$detail['sku']}' ,{$record[$detail['purchaseorder_id']]}, {$detail['quantity']}, {$detail['single_price']}, '{$detail['sku_name']}', 0, 0, 0)";
                    list($detail_id, ) = DB::query(Database::INSERT,$sql)->execute('outland');

                    $skus[$detail_id] = $detail['sku'];
                }
            }

            if(!empty($skus)){
                $sql = "SELECT `goods_id`, `goods_sn` FROM `myr_goods` WHERE `goods_sn` IN ('".join("','", $skus)."')";
                $rels = DB::query(Database::SELECT, $sql)->execute('outland')->as_array('goods_sn', 'goods_id');

                if(!empty($rels)){
                    $ditailIdsStr = '';

                    $sql = 'UPDATE `myr_box_goodsdetail` SET `goods_id` = (CASE `id` ';

                    $have = array_keys($rels);

                    foreach($skus as $id=>$sku){
                        if(in_array($sku, $have)){
                            $sql .= " WHEN {$id} THEN {$rels[$sku]} ";
                            $ditailIdsStr .= empty($ditailIdsStr) ? $id : ','.$id;
                        }
                    }

                    if(!empty($ditailIdsStr)){
                        $sql .= "END) WHERE `id` IN({$ditailIdsStr})";
                        DB::query(Database::UPDATE, $sql)->execute('outland');
                        unset($sql, $ditailIdsStr);
                    }
                }

                return isset($have) ? array_diff($skus, $have) : $skus;
            }
        }

        return $skus;
    }

    /**
     *  params String  $type 'diff' or 'intersect'
     * @return array 6个月内newerp 与 outland的订单ID的diff 与 intersect
     *
     */
    protected function getNewPuIds(){
        $start_time = date("Y-m-d H:i:s",strtotime("-6 month"));
        $end_time   = date('Y-m-d H:i:s');

        $sql= "SELECT `id`
                FROM purchaseorders
                WHERE `purchaseorder_date` <= '{$end_time}'
                AND `purchaseorder_date` > '{$start_time}'
                AND `store` != 0
                AND `status` != 100";
        $newerp_res = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array(NULL,'id');
        $sql = 'SELECT `order_sn`
            FROM myr_deliver
            WHERE `delivery_time` < '.time().' AND `delivery_time` > '.strtotime($start_time)." AND `order_sn` != ''";

        $outland_res = DB::query(Database::SELECT,$sql)->execute('outland')->as_array(NULL,'order_sn');

        return array_diff($newerp_res, $outland_res);
    }

    /**
     *  params String  $type 'diff' or 'intersect'
     * @return array 6个月内newerp 与 outland的订单ID的diff 与 intersect
     *
     */
    protected function getOutlandExistPuIds(){
        $start_time = date("Y-m-d H:i:s",strtotime("-6 month"));
        $end_time   = date('Y-m-d H:i:s');

        $sql= "SELECT `id`
                FROM purchaseorders
                WHERE `purchaseorder_date` <= '{$end_time}'
                AND `purchaseorder_date` > '{$start_time}'
                AND `store` != 0";
        $newerp_res = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array(NULL,'id');
        $sql = 'SELECT `order_sn`
            FROM myr_deliver
            WHERE `delivery_time` < '.time().' AND `delivery_time` > '.strtotime($start_time)." AND `delivery_status` = 1 AND `order_sn` != ''";

        $outland_res = DB::query(Database::SELECT,$sql)->execute('outland')->as_array(NULL,'order_sn');

        return array_intersect($newerp_res, $outland_res);
    }
}